

'''
-- 创建设备 - 用户关联表
CREATE TABLE device_users (
    id SERIAL PRIMARY KEY,
    device_id INT NOT NULL,
    user_id INT NOT NULL,
    FOREIGN KEY (device_id) REFERENCES devices(id),
    FOREIGN KEY (user_id) REFERENCES users(user_id),
    relation_status VARCHAR(20) NOT NULL DEFAULT 'bind', -- 关系状态： 解除绑定 unbind，绑定 bind
    UNIQUE (device_id, user_id),

);

'''

from . import logger
import psycopg2

class DeviceUsersTable:

    def __init__(self, connection, cursor):

        self.connection = connection
        self.cursor = cursor

    # 创建设备 - 用户关联关系
    def create_device_user_relation(self, device_id, user_id):
        try:
            self.cursor.execute("INSERT INTO device_users (device_id, user_id) VALUES (%s, %s)", (device_id, user_id))
            logger.info(f"Device {device_id} has been assigned to user {user_id}.")

        except psycopg2.errors.UniqueViolation as e:
            logger.warning(f"Device {device_id} is already assigned to user {user_id}.")
            return True, None
        
        except psycopg2.Error as e:
            logger.error(f"Error creating device-user relation: {e}")
            return False, str(e)
        
        else:
            return True, None

    # 删除设备 - 用户关联关系
    def delete_device_user_relation(self, device_id, user_id):
        try:
            self.cursor.execute("DELETE FROM device_users WHERE device_id = %s AND user_id = %s", (device_id, user_id))
            logger.info(f"Device {device_id} has been removed from user {user_id}.")
        except psycopg2.Error as e:
            logger.error(f"Error deleting device-user relation: {e}")
            return False
        else:
            return True
        

    # 更新设备 - 用户关联关系
    def update_device_user_relation(self, device_id, user_id, relation_status):
        try:
            self.cursor.execute("UPDATE device_users SET relation_status = %s WHERE device_id = %s AND user_id = %s", (relation_status, device_id, user_id))
            logger.info(f"Device {device_id} has been updated with relation status {relation_status} for user {user_id}.")
        except psycopg2.Error as e:
            logger.error(f"Error updating device-user relation: {e}")
            return False, str(e)
        else:
            return True, None

    # 获取设备 - 用户关联关系
    def get_device_user_relation(self, device_id, user_id):
        try:
            self.cursor.execute("SELECT * FROM device_users WHERE device_id = %s AND user_id = %s", (device_id, user_id))
            result = self.cursor.fetchone()
            if result:
                logger.info(f"Device {device_id} is assigned to user {user_id}.")
                return True, result
            else:
                logger.info(f"Device {device_id} is not assigned to user {user_id}.")
                return False, str(f"Device {device_id} is not assigned to user {user_id}.")
        except psycopg2.Error as e:
            logger.error(f"Error getting device-user relation: {e}")
            return False, str(e)

    # 获取设备 - 用户关联关系列表
    def _get_device_user_relations(self, device_id):
        try:
            self.cursor.execute("SELECT * FROM device_users WHERE device_id = %s", (device_id,))
            result = self.cursor.fetchall()

        except psycopg2.Error as e:
            logger.error(f"Error getting device-user relations: {e}")
            return False, []
        
        else:
            return True, result
        
    def get_device_user_relations(self, device_id):
        try:
            # 修改SQL查询，关联查询device_users和users表
            self.cursor.execute("""
                SELECT du.*, u.user_id, u.user_name 
                FROM device_users du
                JOIN users u ON du.user_id = u.user_id
                WHERE du.device_id = %s
            """, (device_id,))
            result = self.cursor.fetchall()

        except psycopg2.Error as e:
            logger.error(f"Error getting device-user relations: {e}")
            return False, []
        
        else:
            return True, result
        
    # 获取用户 - 设备关联关系列表
    def get_user_device_relations(self, user_id):
        try:
            self.cursor.execute("SELECT * FROM device_users WHERE user_id = %s", (user_id,))
            result = self.cursor.fetchall()

        except psycopg2.Error as e:
            logger.error(f"Error getting user-device relations: {e}")
            return False, []
        
        else:
            return True, result
        